Oracle PL/SQL Programming
Self-paced videos, Lifetime access, Study material, Certification prep, Technical support, Course Completion Certificate
Uplatz
Summary
- Certificate of completion - Free
- Reed courses certificate of completion - Free
Add to basket or enquire
Overview
Uplatz offers comprehensive training on Oracle PL-SQL. This is video-based online course (self-paced training). You will be awarded Course Completion Certificate at the end of the course.
Oracle PL/SQL is an extension of SQL language, specifically speaking it is Procedural language extension to Structured Query Language, and is designed for seamless processing of SQL statements enhancing the security, portability, and robustness of the database. PL/SQL embraces and enhances the SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.
SQL stands for Structured Query Language i.e. used to perform operations on the records stored in database such as inserting records, updating records, deleting records, creating, modifying and dropping tables, views etc.
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.
PL/SQL is a powerful, yet straightforward database programming language. It is easy to both write and read, and comes packed with lots of out-of-the-box optimizations and security features.
This Oracle PL/SQL training by Uplatz will give you great understanding on PL/SQL to work with Oracle database and other advanced RDBMS (Relational Database Management System) concepts.
The PL/SQL online programming, features, and database tuning course explains important aspects of PL SQL language like block structure, data types, packages, triggers, exception handling, etc.
PL/SQL tutorial provides basic and advanced concepts of SQL. Our PL/SQL tutorial is designed for beginners and professionals. PL/SQL is a block structured language that can have multiple blocks in it.
The Oracle PL/SQL training includes all topics of PL/SQL language such as conditional statements, loops, arrays, string, exceptions, collections, records, triggers, functions, procedures, cursors etc. There are also given PL/SQL interview questions and quizzes to help you better understand the PL/SQL language.
Curriculum
Course media
Description
Oracle PL/SQL Course Curriculum
- Overview of PL/SQL
- What is PL/SQL? What are the benefits?
- PL/SQL Block Structure & Types of Blocks
- Writing simple anonymous block
- Using SQL*plus to run anonymous block
- Creating Script and run it
- Quiz
- Declaring PL/SQL Variables
- About Variables and the syntax
- Defining Variables exercise
- Guidelines for declaring & initializing variables
- Delimiters in string literals (the q' Notation)
- Types of variables and data types 1
- Types of variables and data types 2
- Data Types exercise
- Define Variables Using %TYPE
- Understating the SELECT statement in PL/SQL
- Bind Variables
- Quiz
- Writing Executable Statements
- Inside PL/SQL Block
- Inside PL/SQL Block (exercise)
- Nested Blocks
- SQL & PL/SQL programming Guidelines
- Quiz
- Interacting with Oracle DB Server
- SELECT statement & DML in PL/SQL
- SELECT statement & DML in PL/SQL exercise
- Understanding Implicit SQL cursor/ SQL Attributes
- Implicit SQL cursor exercise
- Quiz
- Writing Control Structure
- IF Statement Part 1
- IF Statement Part 2
- Handling nulls in IF statement
- CASE expression & CASE Statement
- Basic Loop
- While Loop
- FOR Loop
- Nested Loops
- Nested Loops and Labels
- Continue Statement
- Working with Composite Data Types
- PL/SQL Records: Programmer method part 1
- PL/SQL Records: Programmer method part 2
- PL/SQL Records: %ROWTYPE method
- Nested PL/SQL Records
- INDEX BY tables (Associative arrays)
- INDEX BY tables Methods
- INDEX BY tables of Records
- Nested table
- VARRAY
- Using Explicit Cursors
- Understanding Explicit Cursor
- Explicit Cursor exercise
- Explicit Cursor Attributes
- FOR LOOP Cursor
- Cursor with Parameters
- FOR UPDATE Clause & CURRENT OF Clause
- Handling Exceptions
- Understanding Exceptions
- Predefined Oracle Server Error Exception
- Common mistakes when dealing with exceptions
- Non-Predefined Oracle Server Error Exception
- User-Defined Error Exception
- Group Functions and exceptions
- Many blocks and many exceptions
- Creating Procedures
- Understanding Procedures and subprograms
- Understanding Procedures Syntax
- Creating Procedure (IN parameter)
- Creating Procedure (OUT parameter)
- Creating Procedure (IN OUT parameter)
- Comparing the Parameter Modes
- Available notations for passing parameters
- Using the default option for Parameters
- Exception handling in multiple blocks
- Using Boolean and PL/SQL records as parameters
- Creating Functions
- Understanding user-defined functions
- Difference between procedures & functions
- Creating function example
- No_data_found and functions
- Advantages of user-defined functions in SQL Statements
- Restrictions when calling functions from SQL part 1
- Restrictions when calling functions from SQL part 2
- Creating Packages
- Understanding packages by nice example
- More about packages
- Creating package practice
- Creating package specification without body
- Guidelines for writing packages
- The visibility of a package’s components
- When & Why to recompile the package?
- Working with Packages
- Understanding Overloading Subprograms in PL/SQL
- Overloading Subprograms practices (procedures)
- Overloading Subprograms practices (functions)
- Overloading Standard package
- Forward Declaration
- Persistent State of packages
- Using PRAGMA SERIALLY_REUSABLE
- Persistent State and cursor
- using PL/SQL tables in packages
- Using Oracle-supplied Packages in Application Development
- About oracle-supplied Packages
- DBMS_OUTPUT part 1
- DBMS_OUTPUT part 2
- UTL_FILE Package Part 1 / create directory
- UTL_FILE Package Part 2 / get_line example 1
- UTL_FILE Package Part 3 / get_line example 2
- UTL_FILE Package Part 4 / put_line
- UTL_FILE Package Part 5 / exceptions
- Using Dynamic SQL
- Execution flow of SQL
- What is Dynamic SQL?
- Execute immediate example 1 / delete any table
- Execute immediate example 2 / DDL in PLSQL
- Execute immediate example 3 / USING Clause
- Execute immediate example 4 / INTO Clause
- Execute immediate example 5 / single row query
- What is REF Cursor?
- Dynamic SQL with multi row query
- Execute immediate/ more examples
- DBMS_SQL Package VS Execute immediate
- Design Consideration for PL/SQL Code
- Standardizing Part 1
- Standardizing Part 2
- Definer’s Rights
- Invoker’s Rights
- Autonomous Transactions
- Features Autonomous Transactions
- The (IN) parameters always passed by reference
- The (out/in out) parameters can be passed by value or reference
- Passing by reference example (IN parameters)
- Passing by value VS passing by reference using nocopy
- When Does NOCOPY hint has no effects?
- NOCOPY Performance Exercise
- Using the PARALLEL_ENABLE hint
- Using the RESULT_CACHE hint
- Using the DETERMINISTIC hint
- Understanding Bulk Binding
- Bulk Binding Example
- Bulk binding with save exception part 1
- Bulk binding with save exception part 2
- Understanding Bulk collect
- Bulk collect another example
- Bulk collect and cursors
- Using returning in bulk collect
- INDICES OF
- Creating Triggers
- Understanding Triggers
- Tigger Types/ Trigger Event Types
- Statement Level trigger VS Row Level (Part 1)
- Statement Level trigger VS Row Level (Part 2)
- Triggers common cases
- Statement Level trigger Example 1
- Statement Level trigger Example 2
- Understanding the NEW & OLD qualifiers
- Row level trigger example
- Row level trigger (create Audit table)
- Trigger Firing sequence
- Compile/enable/ disable/ drop trigger
- Using the triggers for populating a default value
- Instead of triggers (on Views)
Who is this course for?
Everyone
Requirements
Passion and determination to achieve your goals!
Career path
- Oracle Developer
- Oracle DBA (Database Administrator)
- PL/SQL Developer
- Database Developer
- Database Modeller
- Database Programmer
- Metadata Administrator/Developer
- System Administrator
- Accounts Administrator
- Core Operation Engineer (Database)
- Systems Operations Engineer - SQL, Database
- SQL Database Developer
- Data Analyst
- Business Analyst
- Application Developer (back-end part)
- IT/Database Consultant
Questions and answers
Hi, I can learn the course and also obtain the completion certificate.No additional or extra charges is it ? and cost for that is 10 Pounds. is it ?
Answer:Yes, that is right! The course completion certificate is included in the course price. Regards Team Uplatz
This was helpful.hi, I want to know about every thing like installation etc. and when can i start this course. Can you prefer me any book or study material online
Answer:Hi, You'll be getting: - Access within 24 hours - Lifetime access to the course - Course Completion Certification - Study material Regards Team Uplatz
This was helpful.
Certificates
Certificate of completion
Digital certificate - Included
Course Completion Certificate by Uplatz
Reed courses certificate of completion
Digital certificate - Included
Will be downloadable when all lectures have been completed
Reviews
Currently there are no reviews for this course. Be the first to leave a review.
Legal information
This course is advertised on reed.co.uk by the Course Provider, whose terms and conditions apply. Purchases are made directly from the Course Provider, and as such, content and materials are supplied by the Course Provider directly. Reed is acting as agent and not reseller in relation to this course. Reed's only responsibility is to facilitate your payment for the course. It is your responsibility to review and agree to the Course Provider's terms and conditions and satisfy yourself as to the suitability of the course you intend to purchase. Reed will not have any responsibility for the content of the course and/or associated materials.